package com.buyouchina.service.product.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Service;

import com.buyouchina.bean.product.Statistical;
import com.buyouchina.bean.product.Statistical.StatisticalType;
import com.buyouchina.bean.product.Statistical.TongjiAreaType;
import com.buyouchina.service.base.DaoSupport;
import com.buyouchina.service.product.StatisticalService;

@Service
public class StatisticalServiceBean extends DaoSupport<Statistical> implements
		StatisticalService {

	String hql;

	public List<Statistical> find(Integer provinceid, Date date) {
		String hql = "from Statistical s where s.province.id=?1 and s.date=?2";
		return list(hql, provinceid,date);
	}

	public Statistical check(Date startTime,
			Date EndTime,Integer productid, Integer oid) {
		hql = "select s from Statistical s "
				+ "where s.date between ?1 and ?2 and s.product.id=?3 and s.to.id=?4 ";
		List<Statistical> tongjis = list(hql, startTime,EndTime,productid,oid);
		if (tongjis.size() > 0) {
			return tongjis.get(0);
		}
		return null;
	}
	
	public Statistical checkCityRecord(Integer productid, String provinceid, Date startTime,
			Date endTime) {
		List<Statistical> tongjis=new ArrayList<Statistical>();
		if(provinceid!=null){
			hql = "select s from Statistical s "
				+ "where s.date between ?1 and ?2 and s.province.id=?3 and s.product.id=?4";
			tongjis=this.list(hql, startTime,endTime,Integer.parseInt(provinceid),productid);
		}else{
			hql = "select s from Statistical s "
				+ "where s.product.id=?1 and s.date between ?2 and ?3 and s.province.id is null";
			tongjis=this.list(hql, productid,startTime,endTime);
		}
		if (tongjis.size() > 0) {
			return tongjis.get(0);
		}
		return null;
	}
	
	public List<Statistical> list(TongjiAreaType type,Date date,Integer provinceid){
		if(provinceid!=null){
			hql="from Statistical s where s.areaType=?1 and s.date=?2 and s.province.id=?3";
			return list(hql,type,date,provinceid);
		}else{
			hql="from Statistical s where s.areaType=?1 and s.date=?2";
			return list(hql,type,date);
		}
	}
	
	public List<Statistical> listByProduct(Integer productid,TongjiAreaType areaType,StatisticalType type,Date date){
		hql="from Statistical s where s.product.id=?1 and s.areaType=?2 and s.type=?3 and s.date=?4";
		return list(hql, productid,areaType,type,date);
	}

	public List<Statistical> listByDate(Date startTime,Date endTime){
		hql="from Statistical s where s.date between ?1 and ?2";
		return list(hql, startTime,endTime);
	}

	public Integer getCount(Date startTime, Date endTime,
			StatisticalType orderType,Integer pid,Integer epid) {
		Object o = null;

		if (pid != null) {
			hql = "select count(s) from Statistical s inner join s.province p "
					+ "where s.type=?1  and s.date between ?2 and ?3 and "
					+ "p.id between ?4 and ?5";
			o=getCount(hql, startTime,endTime,orderType,pid,epid);
		} else {
			hql = "select count(s) from Statistical s where s.type=?1  and s.date between ?2 and ?3 ";
			o = em.createQuery(hql).setParameter(1, orderType).setParameter(2,
					startTime).setParameter(3, endTime).getSingleResult();
		}
		return ((Long) o).intValue();
	}
	
	

	public List<Statistical> list(Date startTime, Date endTime,
			StatisticalType orderType, Integer pid,Integer epid,String productName, Integer firstResult,
			Integer maxResults) {
		if (pid != null) {
			if(productName!=null){
				hql = "select s from Statistical s inner join s.province p "
					+ "where s.type=?1  and s.date between ?2 and ?3 and "
					+ "p.id between ?4 and ?5 and s.product.name=?6";
			return list(hql, firstResult, maxResults, orderType,startTime,endTime,pid,epid,productName);
			}
			hql = "select s from Statistical s inner join s.province p "
					+ "where s.type=?1  and s.date between ?2 and ?3 and "
					+ "p.id between ?4 and ?5";
			return list(hql, firstResult, maxResults, orderType,startTime,endTime,pid,epid);
		} else {
			if(productName!=null){
				hql = "select s from Statistical s where s.type=?1  and s.date between ?2 and ?3 and s.product.name=?4";
				return list(hql, firstResult, maxResults, orderType,startTime,endTime,productName);
			}
			hql = "select s from Statistical s where s.type=?1  and s.date between ?2 and ?3 ";
			return list(hql, firstResult, maxResults, orderType,startTime,endTime);
		}
	}

	public Integer getTotalCount(Date startTime, Date endTime,
			StatisticalType orderType, Integer pid, Integer epid) {
		if (pid != null) {
			hql = "select count(s) from Statistical s inner join s.province p "
					+ "where s.date between ?1 and ?2 and s.type=?3 and "
					+ "p.id between ?4 and ?5";
			return getCount(hql, startTime,endTime,orderType,pid,epid);
		} else {
			hql = "select count(s) from Statistical s where s.type=?1  and s.date between ?2 and ?3 ";
			return getCount(hql, orderType,startTime,endTime);
		}
	}
	
	public List<Statistical> list(Date startTime,Date endTime,StatisticalType type){
		hql = "select sum(s.quantity),s.product,s.province from Statistical s "
			+"where s.date between ?1 and ?2 and s.type=?3 group by s.product,s.province";
		return list(hql,startTime,endTime,StatisticalType.PURCHASE);
	} 
	
}
